﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using ControleRepresentante.Data.Catalogs;
using ControleRepresentante.Data.Helpers;

namespace ControleRepresentante.Data
{
    public class DataAccess : IDataAccess
    {
        private readonly IDbConnection m_DbConnection;

        public DataAccess(IDbConnection connection)
        {
            m_DbConnection = connection;
        }

        public object ExecuteScalar(string command, params Parameter[] parameters)
        {
            return CreateSqlCommand(command, parameters).ExecuteScalar();
        }

        public IDataReader ExecuteReader(string command, params Parameter[] parameters)
        {
            return CreateSqlCommand(command, parameters).ExecuteReader();
        }

        private IDbCommand CreateSqlCommand(string command, params Parameter[] parameters)
        {
            IDbCommand dbCommand = m_DbConnection.CreateCommand();
            dbCommand.CommandText = command;
            foreach (Parameter parameter in parameters)
            {
                IDbDataParameter dbDataParameter = dbCommand.CreateParameter();
                dbDataParameter.DbType = GetDbType(parameter.Type);
                dbDataParameter.Direction = parameter.IsOutput 
                    ? ParameterDirection.Output 
                    : ParameterDirection.Input;
                dbDataParameter.ParameterName = parameter.Name;
                dbDataParameter.Size = 0;
                dbDataParameter.Value = parameter.Value ?? DBNull.Value;
                dbCommand.Parameters.Add(dbDataParameter);
            }
            return dbCommand;
        }

        public string GetTypeName(ColumnType columnType)
        {
            bool isMySql = m_DbConnection.GetType().Name == "MySqlConnection";
            switch (columnType)
            {
                case ColumnType.Boolean:
                    return "Bit";
                case ColumnType.ShortNumeric:
                    return "Int";
                case ColumnType.LongNumeric:
                    return "BigInt";
                case ColumnType.ShortString:
                    return "nvarchar(255)";
                case ColumnType.LongString:
                    return "nvarchar(4000)";
                case ColumnType.DateTime:
                    return "DateTime";
                case ColumnType.Guid:
                    return isMySql ? "char(36)" : "UniqueIdentifier";
                case ColumnType.Binary:
                    return "Image";
                case ColumnType.LongText:
                    return isMySql ? "text" : "ntext";
                case ColumnType.Float:
                    return "float";
                default:
                    throw new ArgumentException("Invalid ColumnType", "columnType");
            }
        }

        private static DbType GetDbType(ParameterType type)
        {
            switch (type)
            {
                case ParameterType.Int32:
                    return DbType.Int32;
                case ParameterType.Int64:
                    return DbType.Int64;
                case ParameterType.Float:
                    return DbType.Double;
                case ParameterType.String:
                    return DbType.String;
                case ParameterType.Boolean:
                    return DbType.Boolean;
                case ParameterType.DateTime:
                    return DbType.DateTime;
                case ParameterType.Binary:
                    return DbType.Binary;
                case ParameterType.Guid:
                    return DbType.Guid;
                default:
                    throw new ArgumentException("Invalid ParameterType", "type");
            }
        }

        public void ExecuteCommand(string command, params Parameter[] parameters)
        {
            CreateSqlCommand(command, parameters).ExecuteNonQuery();
        }

        public void Dispose()
        {
            m_DbConnection.Close();
        }
    }

    public class DataAccess<TStepType> : DataAccess
    {
        public DataAccess(IDbConnection connection, ICatalog<TStepType> catalog, bool checkCatalog)
            : base(connection)
        {
            if (checkCatalog)
            {
                bool isMySql = connection.GetType().Name == "MySqlConnection";
                if (connection is SqlConnection || isMySql)
                {
                    if (isMySql)
                    {
                        ExecuteCommand("SET SQL_BIG_SELECTS=1");
                    }
                    CreateCatalogStepsTableIfNeeded();
                    long? lastExecutesCatalogStepId = GetLastCatalogStep(catalog);
                    IEnumerable<ICatalogStep> catalogStepsToExecute = GetCatalogStepsToExecute(catalog, lastExecutesCatalogStepId);
                    ExecuteCatalogSteps(catalog, catalogStepsToExecute);
                }
            }
        }

        private void ExecuteCatalogSteps(ICatalog catalog, IEnumerable<ICatalogStep> catalogStepsToExecute)
        {
            foreach (ICatalogStep catalogStep in catalogStepsToExecute)
            {
                catalogStep.Execute(this);
                ExecuteCommand(@"
                    INSERT INTO CatalogSteps (Name, Id, CatalogName)
                    VALUES (@Name, @Id, @CatalogName)",
                    new Parameter("Name", ParameterType.String, catalogStep.Name),
                    new Parameter("Id", ParameterType.Int64, catalogStep.Id),
                    new Parameter("CatalogName", ParameterType.String, catalog.Name));
            }
        }

        private static IEnumerable<ICatalogStep> GetCatalogStepsToExecute(ICatalog<TStepType> catalog, long? lastExecutesCatalogStepId)
        {
            IEnumerable<ICatalogStep> catalogStepsToExecute;
            if (lastExecutesCatalogStepId.HasValue)
            {
                catalogStepsToExecute = catalog.GetSteps()
                    .Where(x => x.Id > lastExecutesCatalogStepId.Value)
                    .OrderBy(x => x.Id);
            }
            else
            {
                catalogStepsToExecute = catalog.GetSteps();
            }
            return catalogStepsToExecute;
        }

        private long? GetLastCatalogStep(ICatalog catalog)
        {
            object lastExecutedStepObject = ExecuteScalar(@"
                SELECT MAX(Id)
                FROM CatalogSteps
                WHERE CatalogName = @CatalogName",
                new Parameter("CatalogName", ParameterType.String, catalog.Name));
            return lastExecutedStepObject == DBNull.Value ? (long?)null : (long)lastExecutedStepObject;
        }

        private void CreateCatalogStepsTableIfNeeded()
        {
            const string CatalogStepsTableName = "CatalogSteps";

            object catalogStepsExist = ExecuteScalar(String.Format(@"
                SELECT 1
                FROM INFORMATION_SCHEMA.TABLES
                WHERE TABLE_NAME = '{0}'", CatalogStepsTableName));
            
            if (catalogStepsExist == null)
            {
                ExecuteCommand(String.Format(@"
                    CREATE TABLE {0}
                    (
                        Id BIGINT NOT NULL PRIMARY KEY,
                        Name nvarchar(255) NOT NULL,
                        CatalogName nvarchar(255) NOT NULL
                    )",
                    CatalogStepsTableName));
            }
        }
    }
}